package com.ymttest.utils;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;

import org.apache.http.client.ClientProtocolException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.shop2cn.iapi.agentproducts.parameter.AddAgentProductBean;
import com.ymt.core.tool.Logger;
import com.ymttest.business.service.AddAgentProductCallService;

public class StockExcleUtil {

	private Workbook wb = null;
	private XSSFSheet sheet = null;
	private Row row = null;
	private Cell cell = null;
	// 当前时间
	private String strCurrentTime = new SimpleDateFormat("yyyyMMddHHmmss").format(Calendar.getInstance().getTime());
	// 文件保存路径 桌面上
	private String strPath = System.getProperty("user.dir") + File.separator + "resource" + File.separator;
	private File file = new File("");

	
	// demo
	public void create() {
		String fileName = "testImport_" + strCurrentTime + ".xlsx";
		wb = new XSSFWorkbook();
		sheet = (XSSFSheet) wb.createSheet("test");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("just to test creater excel file");
		File file = new File(strPath + fileName);
		FileOutputStream fos = null;
		Logger.debug("path:" + strPath + fileName);
		try {
			fos = new FileOutputStream(file);
			wb.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (fos != null) {
				try {
					fos.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	

	// demo
	public List<StockTestCaseBean> readExcel(String path) {
		InputStream io = null;
		try {
			File file = new File(path);
			io = new FileInputStream(file);
			// 2003 xls
			// Workbook wb=new HSSFWorkbook(is);
			// 2007 xlsx格式
			// Workbook wb = new XSSFWorkbook(excelFile);
			wb = new XSSFWorkbook(io);
			sheet = (XSSFSheet) wb.getSheetAt(0);
			List<StockTestCaseBean> lists = new ArrayList<>();
			for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
				XSSFRow row2 = sheet.getRow(i);
				StockTestCaseBean stockTestCaseBean = new StockTestCaseBean();
				stockTestCaseBean.setSku(row2.getCell(0).getStringCellValue());
				stockTestCaseBean.setStocknum(Integer.parseInt(row2.getCell(1).getStringCellValue()));
				stockTestCaseBean.setPrice(Double.parseDouble(row2.getCell(2).getStringCellValue()));
				lists.add(stockTestCaseBean);
			}
			return lists;
			// row = sheet.getRow(0);
			// if (row == null)
			// Logger.debug("row is null");
			// cell = row.getCell(0);
			// String cellStr;
			// if (cell == null) {
			// cellStr = "";
			// } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
			// cellStr = String.valueOf(cell.getBooleanCellValue());
			// } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
			// cellStr = cell.getNumericCellValue() + "";
			// } else {
			// cellStr = cell.getStringCellValue();
			// }
			// Logger.debug("cell str:" + cellStr);
			// // cell.setCellValue("test 2007");
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (io != null) {
				try {
					io.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

		}
		return null;

	}

	public void writeResult(ArrayList<StockTestCaseBean> list) throws IllegalAccessException {
		if (list.size() == 0) {
			Logger.debug("List 为null 退出");
			return;
		}
		String fileName = "ImportTest" + strCurrentTime + ".xlsx";
		wb = new XSSFWorkbook();
		// 创建一个表
		sheet = (XSSFSheet) wb.createSheet("testCase");

		// 废弃的case
		int cancelNum = 0;
		// 忽略的case
		int ignoreNum = 0;
		// 多少列
		int columnNum = 0;
		// 总共case 数
		int totalCase = list.size();

		for (int i = 0; i < totalCase; i++) {
			Class c = list.get(i).getClass();
			Field[] fs = c.getDeclaredFields();
			columnNum = fs.length;
			Row header = null;
			row = sheet.createRow(i + 1);
			// 首先处理title
			if (i == 0) {
				header = sheet.createRow(0);
			}
			for (int j = 0; j < columnNum; j++) {
				Field f = fs[j];
				if (header != null) {
					// 将bean里的字段名作为title 写在第一行
					Cell headerCell = header.createCell(j);
					if (f.getName().equals("sku")) {
						headerCell.setCellValue("商品编码（自编码，必填）");
					}
					if (f.getName().equals("stocknum")) {
						headerCell.setCellValue("入库数量（必填）");
					}
					if (f.getName().equals("price")) {
						headerCell.setCellValue("成本价（选填）");
					}
					// headerCell.setCellStyle(headerStyle);
				}
				cell = row.createCell(j);
				try {
					// 将bean 里字段的value 值作为内容写到excle里
					String value = f.get(list.get(i)).toString();
					cell.setCellValue(value);
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		// 生成文件 路径+文件名
		file = new File(strPath + fileName);
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(file);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			wb.write(fos);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (fos != null)
				try {
					fos.close();
					Logger.info("导出testCase到Desktop:" + fileName + "完成");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}
	
	
	public void CreateProductImport(List<AddAgentProductBean> list) {
		if (list.size() == 0) {
			Logger.debug("List 为null 退出");
			return;
		}
		
		String fileName = "ImportTest" + strCurrentTime + ".xlsx";
		wb = new XSSFWorkbook();
		// 创建一个表
		sheet = (XSSFSheet) wb.createSheet("testCase");

		// 废弃的case
		int cancelNum = 0;
		// 忽略的case
		int ignoreNum = 0;
		// 多少列
		int columnNum = 0;
		// 总共case 数
		int totalCase = list.size();

		for (int i = 0; i < totalCase; i++) {
			Class c = list.get(i).getClass();
			Field[] fs = c.getDeclaredFields();
			columnNum = 32;
			Row header = null;
			row = sheet.createRow(i + 1);
			// 首先处理title
			if (i == 0) {
				header = sheet.createRow(0);
			}
			List<String> asList = Arrays.asList("商品名称（必填）","商品编码（自编码，非必填，商品多规格以分号隔开，库存价格等均对应并以分号隔开）","商品主图（必填，多张图以分号隔开）","商品详情文描","商品详情图片（多张图以分号隔开）","标价币种(填写币种三字码，如人民币CNY)","一级分类（分类不填默认为未分类，若有分类请对照类目ID对照表）","二级分类","三级分类","品牌（未填默认无，若有品牌请对照品牌ID对照表）","税率（未填写则默认包税，税率请对照税率选项对照表）","规格类型1（多规格以分号隔开）","规格类型1属性值","规格类型2（","规格类型2属性值","库存","虚拟库存","销售价","海外专柜价","专柜价币种(填写币种三字码，如人民币CNY)","市场价","代理类型1（未填则默认不填代理价）","代理类型1代理价","代理类型2","代理类型2代理价","代理类型3","代理类型3代理价","物流方式（请对照物流方式对照表）","运费模板（填写运费模板名称，不填则默认包邮）","买家信息（请对照买家信息对照表）","限购数量（不填则不限购）","重量（用于计种运费计算）");
			for (int j = 0; j < columnNum; j++) {
//				Field f = fs[j];
				if (header != null) {
					// 将bean里的字段名作为title 写在第一行
					Cell headerCell = header.createCell(j);
					
					headerCell.setCellValue(asList.get(j));
					
//					
				}
				cell = row.createCell(j);
				try {
					// 将bean 里字段的value 值作为内容写到excle里
//					String value = f.get(list.get(i)).toString();
//					cell.setCellValue(value);
//					headerCell.setCellValue("商品名称（必填）");
//					headerCell.setCellValue("商品编码（自编码，非必填，商品多规格以分号隔开，库存价格等均对应并以分号隔开）");
//					headerCell.setCellValue("商品主图（必填，多张图以分号隔开）");
//					headerCell.setCellValue("商品详情文描");
//					headerCell.setCellValue("商品详情图片（多张图以分号隔开）");
//					headerCell.setCellValue("标价币种(填写币种三字码，如人民币CNY)");
//					headerCell.setCellValue("一级分类（分类不填默认为未分类，若有分类请对照类目ID对照表）");
//					headerCell.setCellValue("二级分类");
//					headerCell.setCellValue("三级分类");
//					headerCell.setCellValue("品牌（未填默认无，若有品牌请对照品牌ID对照表）");
//					headerCell.setCellValue("税率（未填写则默认包税，税率请对照税率选项对照表）");
//					headerCell.setCellValue("规格类型1（多规格以分号隔开）");
//					headerCell.setCellValue("规格类型1属性值");
//					headerCell.setCellValue("规格类型2（");
//					headerCell.setCellValue("规格类型2属性值");
//					headerCell.setCellValue("库存");
//					headerCell.setCellValue("虚拟库存");
//					headerCell.setCellValue("销售价");
//					headerCell.setCellValue("海外专柜价");
//					headerCell.setCellValue("专柜价币种(填写币种三字码，如人民币CNY)");
//					headerCell.setCellValue("市场价");
//					headerCell.setCellValue("代理类型1（未填则默认不填代理价）");
//					headerCell.setCellValue("代理类型1代理价");
//					headerCell.setCellValue("代理类型2");
//					headerCell.setCellValue("代理类型2代理价");
//					headerCell.setCellValue("代理类型3");
//					headerCell.setCellValue("代理类型3代理价");
//					headerCell.setCellValue("物流方式（请对照物流方式对照表）");
//					headerCell.setCellValue("运费模板（填写运费模板名称，不填则默认包邮）");
//					headerCell.setCellValue("买家信息（请对照买家信息对照表）");
//					headerCell.setCellValue("限购数量（不填则不限购）");
//					headerCell.setCellValue("重量（用于计种运费计算）");
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		// 生成文件 路径+文件名
		file = new File(strPath + fileName);
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(file);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			wb.write(fos);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (fos != null)
				try {
					fos.close();
					Logger.info("导出testCase到Desktop:" + fileName + "完成");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}
	//渠道商
		private static int sellerId = AgentUsers.rootUserId;
		private static String sellerName = "NickautoTest";
	public static void main(String[] args) throws Exception {
		StockExcleUtil stockExcleUtil = new StockExcleUtil();
		File file = new File(stockExcleUtil.strPath + "ProductImport.xlsx");
		System.out.println(TestDataManager.getFileMD5(file.getPath()));
		String uploadFile = TestDataManager.UploadFile(file.getPath());
		System.out.println(uploadFile);
//		StockExcleUtil stockExcleUtil = new StockExcleUtil();
//		AddAgentProductBean addAgentProductBean = AddAgentProductCallService.initAddAgentProduct(sellerId,sellerName,false);
		
//		stockExcleUtil.CreateProductImport(Arrays.asList(addAgentProductBean));
//		7ecc29c7049983927cdcb7b0a7a23da9
//		http://file.iapi.ymatou.com/G01/M00/31/A4/rBBlD16Bj1WAcSh4AAAqd9iVOco63.xlsx
	}

	/**
	 * 从输入流中获取字节数组
	 * 
	 * @param inputStream
	 * @return
	 * @throws IOException
	 */
	public static byte[] readInputStream(InputStream inputStream) throws IOException {
		byte[] buffer = new byte[1024];
		int len = 0;
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		while ((len = inputStream.read(buffer)) != -1) {
			bos.write(buffer, 0, len);
		}
		bos.close();
		return bos.toByteArray();
	}

	public static String uploadOne(String sku, int stocknum, double price) throws Exception {
		StockExcleUtil excleUtil = new StockExcleUtil();
		ArrayList<StockTestCaseBean> list = new ArrayList<>();
		StockTestCaseBean testCaseBean = new StockTestCaseBean();
		testCaseBean.setSku(sku);
		testCaseBean.setStocknum(stocknum);
		testCaseBean.setPrice(price);
		list.add(testCaseBean);
		excleUtil.writeResult(list);
		String uploadFile = TestDataManager.UploadFile(excleUtil.file.getPath());
		excleUtil.file.delete();
		return uploadFile;
	}

	public static List<StockTestCaseBean> Download(String uurl) throws Exception {

		URL url = new URL(uurl);
		HttpURLConnection conn = (HttpURLConnection) url.openConnection();
		// 设置超时间为3秒
		conn.setConnectTimeout(3 * 1000);
		// 防止屏蔽程序抓取而返回403错误
		conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
		// 得到输入流
		InputStream inputStream = conn.getInputStream();
		// 获取自己数组
		byte[] getData = readInputStream(inputStream);

		StockExcleUtil stockExcleUtil = new StockExcleUtil();
		// 文件保存位置
		File saveDir = new File(stockExcleUtil.strPath);
		if (!saveDir.exists()) {
			saveDir.mkdir();
		}
		String fileName = "testExport_" + stockExcleUtil.strCurrentTime + ".xlsx";
		File file = new File(saveDir + File.separator + fileName);
		FileOutputStream fos = new FileOutputStream(file);
		fos.write(getData);
		if (fos != null) {
			fos.close();
		}
		if (inputStream != null) {
			inputStream.close();
		}

		List<StockTestCaseBean> readExcel = stockExcleUtil.readExcel(file.getPath());
		return readExcel;
	}

	public static ArrayList<StockTestCaseBean> initBeanList() {
		ArrayList<StockTestCaseBean> list = new ArrayList<>();
		return list;
	}

}

